﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DreamBuilder.UI.UserControls.DataAccess.Services;
using DreamBuilder.Core;
using DreamBuilder.Core.DAO;


namespace DreamBuilder.UI.UserControls.DataAccess.Components
{
    public class MsSqlAttachmentDAL : AttachmentDAL
    {
        public override string AttachmentDBHelperString { get; set; }
        #region 保存附件
        /// <summary>
        /// 保存附件
        /// </summary>
        /// <param name="fileName">附件名称</param>
        /// <param name="attachmentContent">附件内容</param>
        /// <param name="attachmentID">附件编号</param>
        /// <param name="uploadTime">附件上传时间</param>
        /// <param name="documentType">附件类型</param>
        public override void SaveAttachment(string fileName, Byte[] attachmentContent, string attachmentID, 
                                                DateTime uploadTime, string documentType, Hashtable addInfos)
        {
            SqlParameter attachmentContentParam = new SqlParameter("@AttachmentContent", attachmentContent);
            attachmentContentParam.SqlDbType = SqlDbType.Image;

            //Encoding coder = Encoding.GetEncoding("gb2312");
            //byte[] bytes = coder.GetBytes(fileName);
            //fileName =  Encoding.UTF8.GetString(bytes);

            SqlParameter attachmentNameParam = new SqlParameter("@AttachmentName", fileName);
            SqlParameter uploadTimeParam = new SqlParameter("@UploadTime", uploadTime);
            #region 附件实体增加更多信息 20110525
            SqlParameter functionID = new SqlParameter("@FunctionID", addInfos["FunctionID"].ToString());//功能ID
            SqlParameter fileTypeID = new SqlParameter("@FileTypeID", addInfos["FileTypeID"].ToString());//附件文件夹类型
            SqlParameter fileURL = new SqlParameter("@FileURL", addInfos["FileURL"].ToString());//附件存储路径
            SqlParameter isXml = new SqlParameter("@IsXml", addInfos["IsXml"].ToString());//是否XML代码化
            SqlParameter createUserID = new SqlParameter("@CreateUserID", addInfos["CreateUserID"].ToString());//用户ID
            SqlParameter remark = new SqlParameter("@Remark", addInfos["Remark"].ToString());//备注
            
            SqlParameter[] paramArr = new SqlParameter[] { 
                                                            attachmentContentParam, 
                                                            attachmentNameParam, 
                                                            uploadTimeParam,
                                                            functionID,
                                                            fileTypeID,
                                                            fileURL,
                                                            isXml,
                                                            createUserID,
                                                            remark 
            };
            if (!string.IsNullOrEmpty(AttachmentDBHelperString))
            {
                DBHelper = DBHelperFactory.CreateInstance(AttachmentDBHelperString);
            }
            DBHelper.ExecuteSql("insert into P_BT_Attachments(AttachmentID,AttachmentName,UploadTime," +
                                "AttachmentSize,DocumentType,AttachmentContent,FunctionID,FileTypeID," +
                                "FileURL,IsXML,CreateUserID,Remark) values('" + attachmentID + "',@AttachmentName,@UploadTime," + 
                                attachmentContent.Length.ToString() + ",'" + documentType + "',@AttachmentContent," +
                                "@FunctionID,@FileTypeID,@FileURL,@IsXml,@CreateUserID,@Remark)", paramArr);
            #endregion
        }
        #endregion

        #region 获取附件列表
        /// <summary>
        /// 获取附件列表
        /// </summary>
        /// <param name="objectID">附件所属表的记录编号</param>
        /// <param name="objectTableName">附件所属表的名字</param>
        /// <param name="addListValue">增加的附件集合</param>
        /// <param name="deleteListValue">删除的附件集合</param>
        /// <returns>符合条件的附件集合</returns>
        public override DataTable GetAttachmentList(string objectID, string objectTableName,
                                                        string addListValue, string deleteListValue, string FunctionID)
        {
            StringBuilder sb = new StringBuilder();
            if (FunctionID != null && FunctionID != "")
                sb.Append("select AttachmentID,ObjectTableName,ObjectID,AttachmentName,UploadTime,AttachmentSize from P_BT_Attachments where ((ObjectID = '" + objectID + "' and ObjectTableName = '" + objectTableName + "' and FunctionID='" + FunctionID + "')  ");
            else
                sb.Append("select AttachmentID,ObjectTableName,ObjectID,AttachmentName,UploadTime,AttachmentSize from P_BT_Attachments where ((ObjectID = '" + objectID + "' and ObjectTableName = '" + objectTableName + "')  ");
            if (addListValue.Length > 0)
            {
                sb.Append("OR AttachmentID IN (" + addListValue.Remove(addListValue.Length - 1) + ")");
            }
            sb.Append(")");
            if (deleteListValue.Length > 0)
            {
                sb.Append(" AND AttachmentID NOT IN (" + deleteListValue.Remove(deleteListValue.Length - 1) + ")");
            }
            sb.Append(" ORDER BY UploadTime ");
            if (!string.IsNullOrEmpty(AttachmentDBHelperString))
            {
                DBHelper = DBHelperFactory.CreateInstance(AttachmentDBHelperString);
            }
            return DBHelper.ExecuteDataSet(sb.ToString()).Tables[0];
        }
        #endregion
        #region 根据附件ID获取附件信息
        /// <summary>
        /// 根据附件ID获取附件信息
        /// </summary>
        /// <param name="attachmentID">附件ID</param>
        /// <returns>附件信息</returns>
        public override DataTable GetAttachmentByID(string attachmentID)
        {
            string sql = "select AttachmentName,AttachmentContent,DocumentType from P_BT_Attachments where AttachmentID = '" + attachmentID + "'";
            if (!string.IsNullOrEmpty(AttachmentDBHelperString))
            {
                DBHelper = DBHelperFactory.CreateInstance(AttachmentDBHelperString);
            }
            return DBHelper.ExecuteDataSet(sql).Tables[0];
        }
        #endregion

        #region 获取要保存附件信息列表
        /// <summary>
        /// 获取要保存附件信息列表
        /// </summary>
        /// <param name="ObjectID">附件所属表的编号</param>
        /// <param name="ObjectTableName">附件所属表</param>
        /// <param name="addListValue">增加的附件列</param>
        /// <param name="deleteListValue">删除的附件列</param>
        /// <returns>符合条件的信息列表</returns>
        public override DataTable GetSaveDataTable(string ObjectID, string ObjectTableName, 
                                                    string addListValue, string deleteListValue)
        {
            DataTable dtSave = new DataTable ();
            //DataTable dtTmp = new DataTable();

            if (addListValue.Length > 0)
            {
                string sqlstr = "select AttachmentID,ObjectTableName='" + ObjectTableName + "',ObjectID='" + ObjectID + "',RowStatus='" + (int)ROWSTATUS.STATUS_Modify + "' from P_BT_Attachments  where AttachmentID in (" + addListValue.Remove(addListValue.Length - 1) + ")";
                if (!string.IsNullOrEmpty(AttachmentDBHelperString))
                {
                    DBHelper = DBHelperFactory.CreateInstance(AttachmentDBHelperString);
                }
                DataTable dtUpdate = DBHelper.ExecuteDataSet(sqlstr).Tables[0];
                dtSave = dtUpdate.Copy();
            }

            if (deleteListValue.Length > 0)
            {
                string sqlstr = "select AttachmentID,ObjectTableName='" + ObjectTableName + "',ObjectID='" + ObjectID + "',RowStatus='" + (int)ROWSTATUS.STATUS_Delete + "' from P_BT_Attachments  where AttachmentID in (" + deleteListValue.Remove(deleteListValue.Length - 1) + ")";
                if (!string.IsNullOrEmpty(AttachmentDBHelperString))
                {
                    DBHelper = DBHelperFactory.CreateInstance(AttachmentDBHelperString);
                }
                DataTable dtDelete = DBHelper.ExecuteDataSet(sqlstr).Tables[0];
                if (dtSave.Rows.Count > 0)
                {
                    //dtSave.Merge(dtDelete);
                    foreach (DataRow row in dtSave.Rows)
                    {
                        string attachmentId = row["AttachmentID"].ToString();
                        //bool flag = true;
                        foreach (DataRow trow in dtDelete.Rows)
                        {
                            if (attachmentId == trow["AttachmentID"].ToString())
                            {
                                row.Delete();
                                break;
                            }
                        }

                        //if (flag)
                        //{
                        //    DataRow dr = dtTmp.Select("AttachmentID='" + attachmentId + "'")[0];
                        //    dtSave = dr.Table;
                        //}
                    }
                    dtSave.AcceptChanges();
                    dtSave.Merge(dtDelete);
                }
                else
                {
                    dtSave = dtDelete.Copy();
                }
            }

            dtSave.TableName = "P_BT_Attachments";
            dtSave.PrimaryKey = new DataColumn[] { dtSave.Columns["AttachmentID"] };

            return dtSave;
        }
        #endregion
        #region 清除无效记录
        /// <summary>
        /// 清除无效记录
        /// </summary>
        public override void ClearExpireAttachment()
        {
            string sql = " delete from p_bt_attachments where objecttablename is null and objectid is null and uploadtime < getdate()-1 ";
            if (!string.IsNullOrEmpty(AttachmentDBHelperString))
            {
                DBHelper = DBHelperFactory.CreateInstance(AttachmentDBHelperString);
            }
            DBHelper.ExecuteSql(sql);
        }
        #endregion

        #region 是否包含附件
        /// <summary>
        /// 是否包含附件
        /// </summary>
        /// <param name="tableCode">附件所属表名</param>
        /// <param name="pkValue">附件所属表记录ID</param>
        /// <returns>true：包含附件 false：不包含</returns>
        public override bool HaveAttachments(string tableCode, string pkValue)
        {
            string sql = " select * from P_BT_Attachments where ObjectTableName = '" + tableCode + "' and  ObjectID = '" + pkValue + "' ";
            if (!string.IsNullOrEmpty(AttachmentDBHelperString))
            {
                DBHelper = DBHelperFactory.CreateInstance(AttachmentDBHelperString);
            }
            DataSet ds = DBHelper.ExecuteDataSet(sql);
            bool haveAttachments = false;
            if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                haveAttachments = true;
            }
            return haveAttachments;
        }
        #endregion

    }
}


